References
Preface

This is a technical documentation page for the AFM level I exam (April 2019). If you find any mistakes in the content or have suggestions, please let me know. On a side note, here is a link to join the Slack group.

That said, financial models are intended to serve a purpose. They are tools for communicating a specific objective so that users can make informed decisions. This can entail models for project assessments, credit analysis, or evaluations. That being said, the AFM I reporting objectives are based on assessing and predicting cash flows using general purpose financial statements, IFRS or U.S. GAAP compliant.

Overview

"In theory there is no difference between theory and practice. In practice there is."
- Yogi Berra

Financial models are used to aid decision makers. Specifically in this context, assessing and predicting cash flows inherently entail forward-looking assumptions by making reasonable estimates and accounting for cases through scenario analysis. Which means that it is imperative for well-designed financial models to be developed with the appropriate attributes by having meaningful semantic structure, following best-practices, and objectively meeting guidelines. The process is collaborative as multiple stakeholders are involved, so it is important to consider that the models will be reviewd and hence needs to be ledgable.

Attributes - Elements that define a well-designed model:

  1. Dynamic - Assumptions should not be static, and making adjustments should cascade with the model
  2. Flexible - Account for modularity inorder to reduce complexities, simple is elegant
  3. Intuitive - Should reflect the nature of the business accordingly
  4. Transparent - Flows in logical order
  5. Printable - Easy to format
  6. Transferable - Usable and material to other stakehodlers

Structure - More of an art than a science, but a boilerplate model includes:

  1. Cover - Meta data
  2. Summary - Key metrics
  3. Assumptions - Variables
  4. Scenarios - Conditionals
  5. Model - Results conveyed through financial statements
  6. Outputs - Ratio/valuation analysis

Best-practices - Formatting (i.e., color coding, fonts, formulas, currency, units, percents, etc.)

Model Planning, Setup and Assumptions
  1. Choose a public company. Read their most recent annual report (i.e., 10K)
  2. Create an Excel file with the following five tabs: Cover, Summary, Assumptions, Scenarios, and Model.
  3. Enter the trailing three years of the company's historical financial statements (income, cash flow, balance sheet)
  4. Analyze and synthesize the necessary assumptions required to forecast the financial statements under the "Assumptions" tab.
  5. Create scenarios for critical variables

Topics: Model structure, design, planning, inputs, assumptions, scenario analysis

Revenues
  1. Forecast revenue as a function of price and sales volume
  2. If the company has multiple operating segments, apportion the revenue schedule for the ones that are material and group the ones that are insignificant which can be forecasted by an implied growth rate
  3. Factor in any discounts or deductions that affect revenues

Topics: Prices and sales volume, gross and net revenues, capacity constraints

Operating Costs and Depreciation Expense
  1. Create an operating cost schedule to captures the fixed and variable cost and a depreciation schedule to forecast depreciation expense
  2. Forecast variable costs on a per-unit basis
  3. Forecast fixed costs on an aggregate basis
  4. By capturing the company's cost structure, evaluate their operating leverage
  5. Calculate gross profit, EBITDA, and operating profit
  6. Evaluate the depreciation methodologies used. If it is not disclosed, apply the most appropriate.
  7. Use a lookup function to display the company's CAPEX vertically
  8. Build a waterfall depreciation schedule to calculate the forecasted CAPEX

Topics: Fixed and variable operating costs, operating leverage, income statement, depreciation methods, CAPEX

Income Tax Expense and Working Capital
  1. Create a tax schedule to forecast cash tax and deferred tax expense and a working capital schedule to forecast non-interest bearing short-term assets and liabilities
  2. Forecast depreciation expense relative to the comapny's jurisdiction
  3. Calculate pre-tax income used for financial reporting and tax-book conformity
  4. Calculate the number of days per period using the date function
  5. Calculate the historical days for each of the working capital accounts (i.e., days receivable, days inventory, and days payable)
  6. Forecast the working capital balances for each account into the future
  7. Calculate the change in working capital, which will effect the cash flow statement

Topics: Timing differences between accounting and government rules, cash taxes vs. deferred taxes, deferred tax liabilities, working capital

Debt, Interest and Shareholders' Equity
  1. Create the schedules to forecast the company's debts, interest expense, and shareholders' equity
  2. Segment each debt on the schedule which will appear on the balance sheet
  3. Segment both short-term and long-term debt
  4. Include a section for bank debts (i.e., revolving credit facility)
  5. Calculate interest expense
  6. Segment each equity item on the equity schedule that will appear on the balance sheet. Including sections for common shares, preferred shares, and retained earnings.
  7. Forecast any dividends or preferreds if applicable and include these items on the cash flow statement
  8. Include a section to calculate shares outstanding which accounts for any share issuance or share repurchases

Topics: Debt types, fixed and variable interest rates, equity types, dividends, share dilution and repurchases

Financial Statements
  1. Forecast the financial statements
  2. Integrate all the appropriate items from the schedules created
  3. Decide whether or not to include circularity

Topics: Financial statement composition, model circularity

Summary Page, Formatting, Presentation and Speed
  1. Create a summary page to output key statistics and format for presentation
  2. Create a summary table which includes output for the base case, best case, and worst case scenarios
  3. Changes made to the model should automatically update the summary page
  4. Add headers and footers onto each page
  5. Setup print ranges

Topics: Formatting

Model Troubleshooting, Fixing Erros and Avoiding Pitfalls
  1. Resolve any errors by stress testing the model
  2. Review key skills and audit a well-designed financial model for comparison
  3. Understand how to find errors that will prevent the balance sheet from balancing
  4. Review best-practices and ensure the model was built effectively

Topics: Model auditing

Suggested Readings